{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EntitySets and Feature Engineering\n",
    "\n",
    "In this tutorial, we will go over the basics of Featuretools. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting started \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import Featuretools Library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import featuretools as ft"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Working with EntitySets\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this example, we will use a dataset of retail data of customers from a UK website from December 2010 to December 2011.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'demo_retail_data'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es = ft.demo.load_retail(nrows=1000)\n",
    "es.id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List entities in the EntitySet\n",
    "An entity is a single table of data.  Each row is an \"instance\" of our entity and ach column is a \"variable\". "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "invoices\n",
      "items\n",
      "customers\n",
      "item_purchases\n"
     ]
    }
   ],
   "source": [
    "for e in es.entities:\n",
    "    print e.name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "see variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show first n rows of an entity\n",
    "We control the number of rows with ``n``. By default, ``n=10``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_purchase_id</th>\n",
       "      <th>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_purchase_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>536365</td>\n",
       "      <td>85123A</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>2.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>536365</td>\n",
       "      <td>71053</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>536365</td>\n",
       "      <td>84406B</td>\n",
       "      <td>8</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>536365</td>\n",
       "      <td>84029G</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>536365</td>\n",
       "      <td>84029E</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  item_purchase_id InvoiceNo StockCode  Quantity  \\\n",
       "item_purchase_id                                                   \n",
       "0                                0    536365    85123A         6   \n",
       "1                                1    536365     71053         6   \n",
       "2                                2    536365    84406B         8   \n",
       "3                                3    536365    84029G         6   \n",
       "4                                4    536365    84029E         6   \n",
       "\n",
       "                         InvoiceDate  UnitPrice  \n",
       "item_purchase_id                                 \n",
       "0                2010-12-01 08:26:00       2.55  \n",
       "1                2010-12-01 08:26:00       3.39  \n",
       "2                2010-12-01 08:26:00       2.75  \n",
       "3                2010-12-01 08:26:00       3.39  \n",
       "4                2010-12-01 08:26:00       3.39  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es[\"item_purchases\"].head(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Show specific instance (row) of entity\n",
    "If we know a row we want to see, we can use ``show_instance``."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_purchase_id</th>\n",
       "      <th>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_purchase_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>100</td>\n",
       "      <td>536378</td>\n",
       "      <td>84519A</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-12-01 09:37:00</td>\n",
       "      <td>2.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  item_purchase_id InvoiceNo StockCode  Quantity  \\\n",
       "item_purchase_id                                                   \n",
       "100                            100    536378    84519A         6   \n",
       "\n",
       "                         InvoiceDate  UnitPrice  \n",
       "item_purchase_id                                 \n",
       "100              2010-12-01 09:37:00       2.95  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es[\"item_purchases\"].show_instance(100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Accessing Variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### List variables in entity\n",
    "\n",
    "If we want to list all the variables of an entity, which is equivalent to all of the columns in the entities table, we do as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Variable: first_item_purchases_time (dtype: datetime, format: None)>,\n",
       " <Variable: CustomerID (dtype = categorical, count = 65)>,\n",
       " <Variable: InvoiceNo (dtype = categorical, count = 66)>]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es[\"invoices\"].variables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Access a variable in an entity\n",
    "We can access certain variables as well. This will become useful later for feature engineering and analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Variable: UnitPrice (dtype = numeric, count = 1000)>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es[\"item_purchases\"][\"UnitPrice\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Variable Statistics\n",
    "We can view statistics about a variable such as the price of the items."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "165.0\n",
      "0.0\n"
     ]
    }
   ],
   "source": [
    "print es[\"item_purchases\"][\"UnitPrice\"].max\n",
    "print es[\"item_purchases\"][\"UnitPrice\"].min"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For a given variable, we can see the first n values using head"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UnitPrice</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_purchase_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  UnitPrice\n",
       "item_purchase_id           \n",
       "0                      2.55\n",
       "1                      3.39\n",
       "2                      2.75\n",
       "3                      3.39\n",
       "4                      3.39"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es[\"item_purchases\"][\"UnitPrice\"].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Different Variable Types\n",
    "Variables can be many different types. The example above of item prices is a numeric variable, but there are also categorical, datetime, ordinal, and text variables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Variable: item_purchase_id (dtype = categorical, count = 1000)>\n",
      "<Variable: InvoiceDate (dtype: datetime, format: None)>\n"
     ]
    }
   ],
   "source": [
    "print es[\"item_purchases\"]['item_purchase_id']\n",
    "print es[\"item_purchases\"]['InvoiceDate']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### View Relationships\n",
    "The entities are connected by relationships between variables. A relationship is a parent-child relationship between entities. For example, the third relationship shown below shows that the purchase entity is a child of the customers entity. This means that each customer can have multiple purchases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Relationship: item_purchases.StockCode -> items.StockCode>,\n",
       " <Relationship: item_purchases.InvoiceNo -> invoices.InvoiceNo>,\n",
       " <Relationship: invoices.CustomerID -> customers.CustomerID>]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es.relationships"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "these relationships are analogues to foreign-key relationship in a SQL database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Building Features\n",
    "\n",
    "### A feature is any new value we create by transforming our raw data\n",
    "The simplest way to build a feature is to just use a variable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: Country>"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "region = ft.Feature(es[\"customers\"][\"Country\"])\n",
    "region"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating More Complicated Features\n",
    "We can create more complicated features as well. New features can be defined using previously defined features.\n",
    "\n",
    "We will add onto previous simpler features to create more complicated ones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: Country = Germany>"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "germany = region == 'Germany'\n",
    "germany"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The feature above is a feature with a boolean value (True or False) depending on whether the customer is from germany."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: Country = Germany OR Country = Italy>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "italy = region == 'Italy'\n",
    "germany_or_italy = germany | italy\n",
    "germany_or_italy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Viewing feature values\n",
    "\n",
    "We can view feature values like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country = Germany OR Country = Italy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12431.0</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16029.0</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16098.0</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16210.0</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16218.0</th>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Country = Germany OR Country = Italy\n",
       "12431.0                                   0.0\n",
       "16029.0                                   0.0\n",
       "16098.0                                   0.0\n",
       "16210.0                                   0.0\n",
       "16218.0                                   0.0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "germany_or_italy.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Featuretools has many functions such as ``multiply``, ``divide``, ``add``, ``subtract`` that can be used to create more complicated features. For example we can multiply price and quantity of a purchase to calculate the total cost."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UnitPrice * Quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_purchase_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662</th>\n",
       "      <td>6.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>661</th>\n",
       "      <td>6.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>8.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>659</th>\n",
       "      <td>1.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>17.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>666</th>\n",
       "      <td>3.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>660</th>\n",
       "      <td>1.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>667</th>\n",
       "      <td>2.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>25.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  UnitPrice * Quantity\n",
       "item_purchase_id                      \n",
       "0                                15.30\n",
       "662                               6.30\n",
       "661                               6.30\n",
       "656                               8.50\n",
       "659                               1.10\n",
       "655                              17.70\n",
       "666                               3.40\n",
       "660                               1.65\n",
       "667                               2.55\n",
       "654                              25.50"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price = ft.Feature(es['item_purchases']['UnitPrice'])\n",
    "quantity = ft.Feature(es['item_purchases']['Quantity'])\n",
    "total_item_cost = price * quantity\n",
    "total_item_cost.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Stacking and Creating Aggregation Features\n",
    "We can stack features by aggregating features of a child entity. We use the 'Sum' aggregation for the cost of each item purchase to get the cost of total purchase. Unlike a SQL groupby and join, Featuretools handles the grouping of the child entity automatically"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: SUM(item_purchases.UnitPrice * Quantity)>"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from featuretools.primitives import Sum\n",
    "\n",
    "total_invoice_cost = Sum(total_item_cost, es['invoices'])\n",
    "total_invoice_cost"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can aggregate once again on each of the purchases of the customer to get the\n",
    "* largest purchase made by the customer in the time span using 'Max' aggreagation \n",
    "* total spent by the customer in the time span using 'Sum' aggreagation "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from featuretools.primitives import Max\n",
    "\n",
    "cost_largest_purchase = Max(total_invoice_cost, es['customers'])\n",
    "total_spent = Sum(total_invoice_cost, es['customers'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(invoices.SUM(item_purchases.UnitPrice * Quantity))</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12431.0</th>\n",
       "      <td>358.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16029.0</th>\n",
       "      <td>3702.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16098.0</th>\n",
       "      <td>430.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SUM(invoices.SUM(item_purchases.UnitPrice * Quantity))\n",
       "12431.0                                             358.25     \n",
       "16029.0                                            3702.12     \n",
       "16098.0                                             430.60     "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_spent.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead of aggregating multiple times over parent enitities, aggregation can also be done in one aggregation as shown below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total_spent = Sum(total_item_cost, es['customers'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice the table below is equivalent to the one above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(item_purchases.UnitPrice * Quantity)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12431.0</th>\n",
       "      <td>358.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16029.0</th>\n",
       "      <td>3702.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16098.0</th>\n",
       "      <td>430.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         SUM(item_purchases.UnitPrice * Quantity)\n",
       "12431.0                                    358.25\n",
       "16029.0                                   3702.12\n",
       "16098.0                                    430.60"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_spent.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Datetime Features\n",
    "\n",
    "We will explore the and create features using the datetime column in the \"invoices\" entity. The column \"InvoiceDate\" is the time of the purchase."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>first_item_purchases_time</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>InvoiceNo</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>536365</th>\n",
       "      <td>2010-12-01 08:26:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536366</th>\n",
       "      <td>2010-12-01 08:28:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536367</th>\n",
       "      <td>2010-12-01 08:34:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536368</th>\n",
       "      <td>2010-12-01 08:34:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536369</th>\n",
       "      <td>2010-12-01 08:35:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          first_item_purchases_time\n",
       "InvoiceNo                          \n",
       "536365          2010-12-01 08:26:00\n",
       "536366          2010-12-01 08:28:00\n",
       "536367          2010-12-01 08:34:00\n",
       "536368          2010-12-01 08:34:00\n",
       "536369          2010-12-01 08:35:00"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "es[\"invoices\"][\"first_item_purchases_time\"].head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We will create a feature that computes the month of the purchase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MONTH(first_item_purchases_time)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>InvoiceNo</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>536365</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536400</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536401</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536402</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536403</th>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           MONTH(first_item_purchases_time)\n",
       "InvoiceNo                                  \n",
       "536365                                   12\n",
       "536400                                   12\n",
       "536401                                   12\n",
       "536402                                   12\n",
       "536403                                   12"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from featuretools.primitives import Month\n",
    "\n",
    "month_of_purchase = Month(es[\"invoices\"][\"first_item_purchases_time\"])\n",
    "month_of_purchase.head(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "let's assume we are interested in consumers who shopped on the weekends in December leading up to the xmas holidays. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Feature: IS_WEEKEND(first_item_purchases_time)>\n",
      "<Feature: DAY(first_item_purchases_time) < 25>\n",
      "<Feature: MONTH(first_item_purchases_time) = 12 AND IS_WEEKEND(first_item_purchases_time) AND DAY(first_item_purchases_time) < 25>\n"
     ]
    }
   ],
   "source": [
    "from featuretools.primitives import Weekend, Day\n",
    "\n",
    "weekend_purchase = Weekend(es[\"invoices\"][\"first_item_purchases_time\"])\n",
    "print weekend_purchase\n",
    "purchase_before_25th = Day(es[\"invoices\"][\"first_item_purchases_time\"]).LT(25)\n",
    "print purchase_before_25th\n",
    "holiday_shopping = (month_of_purchase == 12) & weekend_purchase & purchase_before_25th\n",
    "print holiday_shopping"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Renaming Features\n",
    "Feature names can get hard to read, so we want to assign new names to features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: is_holiday>"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holiday_shopping = holiday_shopping.rename(\"is_holiday\")\n",
    "holiday_shopping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>is_holiday</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>InvoiceNo</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>536365</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536400</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536401</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536402</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>536403</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           is_holiday\n",
       "InvoiceNo            \n",
       "536365          False\n",
       "536400          False\n",
       "536401          False\n",
       "536402          False\n",
       "536403          False"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "holiday_shopping.head(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Timesince Feature \n",
    "Suppose we are interested in looking at amount of time since the customer's most recent purchase. To do this, we can calculate a customer's last purchase and then apply the TimeSince feature."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time_since_last_invoice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12431.0</th>\n",
       "      <td>2410.244215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16029.0</th>\n",
       "      <td>2410.247687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16098.0</th>\n",
       "      <td>2410.256715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16210.0</th>\n",
       "      <td>2410.144215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16218.0</th>\n",
       "      <td>2410.184493</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16250.0</th>\n",
       "      <td>2410.246993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16552.0</th>\n",
       "      <td>2410.137965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16583.0</th>\n",
       "      <td>2410.160881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17181.0</th>\n",
       "      <td>2410.136576</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17377.0</th>\n",
       "      <td>2410.138659</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         time_since_last_invoice\n",
       "12431.0              2410.244215\n",
       "16029.0              2410.247687\n",
       "16098.0              2410.256715\n",
       "16210.0              2410.144215\n",
       "16218.0              2410.184493\n",
       "16250.0              2410.246993\n",
       "16552.0              2410.137965\n",
       "16583.0              2410.160881\n",
       "17181.0              2410.136576\n",
       "17377.0              2410.138659"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from featuretools.primitives import Last, TimeSince\n",
    "\n",
    "last_invoice = Last(es[\"invoices\"][\"first_item_purchases_time\"], es[\"customers\"])\n",
    "time_since = TimeSince(last_invoice).rename(\"time_since_last_invoice\")\n",
    "time_since.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Comparing a Feature"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we are interested whether the unit price of a purchased item is above $2.50 we would compute as shown below"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UnitPrice &gt; 2.5</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>item_purchase_id</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>662</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>661</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>656</th>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>659</th>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  UnitPrice > 2.5\n",
       "item_purchase_id                 \n",
       "0                            True\n",
       "662                         False\n",
       "661                         False\n",
       "656                          True\n",
       "659                         False"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price = ft.Feature(es[\"item_purchases\"][\"UnitPrice\"])\n",
    "price_gt_5 = price > 2.50\n",
    "price_gt_5.head(n=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Limiting Duration of Historical Data \n",
    "\n",
    "Suppose we are only interested in the average purchase amount of the purchases the customer made in the last month. We can add a use previous parameter of 30 days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: MEAN(invoices.total, Last 30 Days)>"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from featuretools.primitives import Mean \n",
    "\n",
    "total_invoice = Sum(total_item_cost, es['invoices']).rename(\"total\")\n",
    "\n",
    "average_purchase_last_month = Mean(total_invoice, es['customers'],\n",
    "                                   use_previous=\"30 days\")\n",
    "average_purchase_last_month"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Conditional Summing | Adding the 'where' Parameter\n",
    "\n",
    "Suppose we are interested in the total amount the customer spent for holiday shopping. We can use the 'where' parameter adding the Boolean Feature \"holiday_shopping\" that we created previousily."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: SUM(invoices.total WHERE is_holiday)>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_holiday_purchase = Sum(total_invoice,\n",
    "                             es['customers'], where=holiday_shopping)\n",
    "total_holiday_purchase"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SlidingWindow Feature\n",
    "Sometimes we would like to use more than a single aggregate value from a child entity.\n",
    "\n",
    "SlidingWindow features let us define windows of time on child entities with which to aggregate data. Unlike standard AggregationFeatures such as Mean and Max that return a single value for each parent instance, SlidingWindow features return a fixed size array of values, referred to as windows.\n",
    "\n",
    "In our example below the feature will return 3 values since window_size is 3 times smaller than use_previous.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Feature: SLIDING_MEAN(item_purchases.UnitPrice * Quantity, Last 30 Days, window_size = 10 Days)>"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from featuretools.primitives import SlidingMean\n",
    "SlidingMean(total_item_cost, es['customers'], use_previous='30 days', window_size='10 days')"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
